Report Parameters – Part 1
Query parameters let you create a dynamic query that can accept user input when the query is published as a report. For instance, allowing users to specify the date they want to use to filter the report output.
Let’s add the “@StartDate” parameter to our “Clients Born Before Date” query. First we’ll modify the query and review the results; then we’ll review parameters in more detail.
1. Revise your query to look like this:
SELECT * FROM tblClient
WHERE colDateofBirth < @StartDate
2. In the Preview Options section beneath the Query memo field, enter 1970-01-01 in the Start Date field. This is the date that will be used when you preview your query results. (This date value won’t be used when you later publish your query as a report.)
3. Select the Preview button to preview the results:
Your query results are still limited to clients born before 1970-01-01, because you used that date as your date parameter.
4. Close the query preview page to return to the query-building page, and change the Start Date field in the Preview Options section to 1980-01-01.
5. Select the Preview button to preview the results:
Your query results should have increased to include all clients born before 1980-01-01.
By adding the “@StartDate” parameter to your query, you can select any date you want to filter the results. When the query is published as a report, users will also be able to specify the date they want used.
Close the query preview page to return to the query-building page, and continue to “Query Parameters – Part 2” to learn more about other parameters you can use in your queries.